﻿'Copyright (c) 2009 Grailtek

'Permission is hereby granted, free of charge, to any person
'obtaining a copy of this software and associated documentation
'files (the "Software"), to deal in the Software without
'restriction, including without limitation the rights to use,
'copy, modify, merge, publish, distribute, sublicense, and/or sell
'copies of the Software, and to permit persons to whom the
'Software is furnished to do so, subject to the following
'conditions:

'The above copyright notice and this permission notice shall be
'included in all copies or substantial portions of the Software.

'THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
'EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
'OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
'NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
'HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
'WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
'FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
'OTHER DEALINGS IN THE SOFTWARE.

Imports System.Reflection
Imports System.Data.Common
Imports System.Data.SqlClient
Imports System.ExecutorServices
Imports System.DataExecutorServices.Exceptions
Imports System.Aspect

<AttributeUsage(AttributeTargets.Delegate)> _
Public MustInherit Class SqlDataCallAttribute
    Inherits DataCallBaseAttribute

    Public Sub New(ByVal iUIName As String, ByVal iStoredProcedureName As String, ByVal iConnectionString As String)
        MyBase.New(iUIName, iStoredProcedureName, GetDatabaseFromConnectionString(iConnectionString), iConnectionString)
    End Sub

    Protected Shared Function GetDatabaseFromConnectionString(ByVal ConnectionString As String) As String
        Dim connection As New SqlConnection(ConnectionString)
        Return connection.Database.ToString()
    End Function

    Public Sub New(ByVal iUIName As String, ByVal iStoredProcedureName As String, ByVal iDatabase As String, ByVal iConnectionString As String)
        MyBase.New(iUIName, iStoredProcedureName, iDatabase, iConnectionString)
    End Sub

    Protected Overrides Sub Execute(ByVal args() As Argument)
        Using cmd As SqlClient.SqlCommand = GetSQLCmd(args)
            cmd.Connection.Open()
            Try
                cmd.ExecuteNonQuery()
            Catch sqlEx As SqlException
                HandleSqlEx(sqlEx)
            Finally
                cmd.Connection.Close()
                cmd.Connection.Dispose()
            End Try
        End Using
    End Sub

    Protected Overrides Function ExecuteReturnTable(ByVal args() As Argument) As DataTable
        Dim ret As DataTable = Nothing
        Using cmd As SqlClient.SqlCommand = GetSQLCmd(args)
            Using da As New SqlDataAdapter(cmd)
                Using ds As New DataSet
                    cmd.Connection.Open()
                    Try
                        da.Fill(ds)
                    Catch sqlEx As SqlException
                        HandleSqlEx(sqlEx)
                    Finally
                        cmd.Connection.Close()
                        cmd.Connection.Dispose()
                    End Try
                    If ds.Tables.Count > 0 Then ret = ds.Tables(0)
                End Using
            End Using
        End Using
        Return ret
    End Function

    'TODO: hook this into the messaging system (an aspect) as a request to show the user a message
    Protected Shared Sub HandleSqlEx(ByVal sqlEx As SqlException)
        If sqlEx.Class = 16 Then
            Select Case sqlEx.Number
                Case 201
                    Throw New DbUserException(sqlEx) 'TODO: transform this into an error message than shows UI names of both the function and the param
                Case 50000
                    Throw New DbUserException(sqlEx) '50000 is the traditional number for end-user errors
            End Select
        End If
        Throw sqlEx
    End Sub

    Protected Function GetSqlConnection() As SqlConnection
        Return New SqlConnection(ConnectionString)
    End Function

    Private Function GetSQLCmd(ByVal args() As Argument) As SqlCommand
        Dim ret As New SqlClient.SqlCommand(If(String.IsNullOrEmpty(Database), "", "[" & Database & "].") & "dbo.[" & RegisterName & "]", GetSqlConnection)
        ret.CommandType = CommandType.StoredProcedure
        For Each arg As Argument In args
            Dim ArgValue As Object = arg.Value
            If Not ArgValue Is Nothing AndAlso ArgValue.GetType() Is GetType(DateTime) Then
                If DirectCast(ArgValue, DateTime).Ticks = 0 Then ArgValue = DBNull.Value
            End If
            Dim RegName = String.Empty
            If Not arg.ParamAttribute Is Nothing Then RegName = arg.ParamAttribute.RegisterName
            If String.IsNullOrEmpty(RegName) Then RegName = arg.Param.Name
            Dim p As New SqlParameter("@" & RegName, ArgValue)
            Try
                'cause validation of incoming type, access to the dbtype property will throw an exception if SQL doesn't like it
                'if this is not done here then this exception will be thrown less usefully further downstream
                IIf(p.DbType = Nothing, Nothing, Nothing)
            Catch ex As ArgumentException
                Throw New System.DataExecutorServices.Exceptions.DbTypeMappingException(arg.Param.ParameterType, ex)
            End Try
            ret.Parameters.Add(p)
        Next
        Return ret
    End Function

End Class